FAQ's - Flex Reports
- What is a Flex Report?
- What type of features are supported by Flex Reports?
- How do I setup a new Flex Report?
- What permissions are needed to view Flex Reports?
- Do Flex Reports consider a User's Search Criteria?
- How do I see all the Flex Reports setup in TBS?
- How do I see all Reports a User has access to?
- How do I turn on a Flex Report for a Customer, Wholesale or Agent user?
- Can Flex Reports pull data from other database sources?
- Why does the Flex Report only return 1000 records?
Flex Reports are custom queries that can be setup to report on almost every single data field in the TBS and supporting TBS databases. Flex Reports can be simple queries such as a Customer List, a Trouble Ticket list, etc. or can be even more advanced by utilizing parameters to find specific snapshots of current or billing history data.
Flex reports are setup using SQL statements that are then stored in the database and executed when the Report is requested. This setup allows for new reports to be setup without the need for a code release. The Flex Report engine is backed by a performance saver design that will regulate the result set and ensure that regardless of data records returned, the TBS service will properly handle the performance load. See "Why does the Flex Report only return 1000 records?"
In addition, Flex supports the power of "PreSQL" and "FixSQL". PreSQL is used for very complex queries that require data to be pulled together and structured prior to reporting the results to the user. This method creates temporary tables to hold the complex data manipulations so that it can then be reported on and possibly have updates ran using this complex, temporary structure.
FixSQL is used to apply updates to either a regular Flex Report result set or to a PreSQL temporary snapshot of data. FixSQL queries can apply updates to the result sets based on input parameters by the user. An example of FixSQL might be to mass update the rate plan on all LD lines for an Account. The Report would ask for an account number and a rate plan name. Running the Flex Report would show all LD lines for the Account. The user can then select all lines or individual lines they want to apply the "FixSQL" to. They can then run the FixSQL portion of the report to apply the updates outlined in the FixSQL query.
Please note that Flex Reports are very powerful and great care should be used when running any report. Please be sure to reach out to Precision support for assistance in setting up new Flex Reports.
What type of features are supported by Flex Reports?
Flex Reports utilize an advanced results grid component that allows for flexible data manipulation of all reports. Here is a list of new features that are available on all Flex Reports.
Report Grid Features
- Grouping - a user can drag a column up to the header and group by that column. Note: Money fields can be setup as currency that will also provide a Sum amount for each group. Please also note that if a column name has a space in the title, it cannot be used for grouping. An easy fix would be to remove the space in the column title.
- Sorting - a user can click on a column name to sort ascending, descending, or cancel a sort.
- Exporting - a user has a variety of export options available for extracting data results. See Exporting below
- Filtering - a user can enter a filter expression at the top of the column grid and filter down the data that is displayed. To enable a filter, type the filter text into the column filter textbox, then click the filter icon to select the filter application (i.e., “Greater than”, “Equal to”, etc.). The grid will automatically update.
- Paging - reports that return more than 20 records will have paging ability at the top and bottom of the report grid. Additional paging features include a "Go To" exact page and the ability to update the number of records returned per page.
- Selection across Pages - records can now be selected across multiple pages within the returned result set. Selected records will be saved for exporting or applying FixSQL to only selected records
- Select All Page Records / Select All Grid Records - reports that have paging enabled will support both a page level "select all" via a checkbox in the header row of the grid. In addition, options will be available to select all returned grid rows (max 1000).
All Grid Features will remain intact even when new Parameter filters are applied. To clear all parameters and filters, please click the report link at the top of the page to start over.
Exporting Data Results
Exporting results come in a variety of ways:
- Current Page (Word, Excel, Excel Html, PDF, CSV) – this will only export the results shown on the current grid page.
- Entire Grid (Word, Excel, Excel Html, PDF, CSV) – this will export all results returned in the entire grid. Note: The maximum number of records exported with this feature is 1000.
- Selected Records (Word, Excel, Excel Html, PDF, CSV) - this will export all records that have been selected throughout the entire grid.
- Full Result Set - uses the TBS backend service, and only supports CSV – this option will only be available if more than 1000 records matched the report results. This file type, for most users, should automatically open in Excel.
Note: The Excel export option will export all data into a spreadsheet with no formatting, however the general grouping and sorting will remain intact. Any sum totals however will not be exported. To keep the group sum totals, be sure to use the ExcelHTML option.
How do I setup a new Flex Report?
Flex Reports Maintenance tools are available within TBS under the Admin - Administration Menu (new) link.
Since the Flex Reports module is still being upgraded to include a more user friendly setup interface, it is advised that new reports and changes to existing reports be made with the help of Precision support.
To help in understanding and to gain familiarity with how the Report maintenance tools function, additional information can be found by viewing the Flex Report Management Tool help topic within the User Guide accessible from the Admin Menu.
What permissions are needed to view Flex Reports?
Flex Reports are tightly integrated into the security module of TBS. Flex Reports use the same T4Groups security modules as those on the Users maintenance screen. Therefore, Users automatically have access to reports based on the Flex Modules tied to the Reports. For instance, if a user has access to the Customers security module, they will have access to any / all reports that are setup with the Flex Module "Customers".
Within the Flex Report Administration tools, there are a variety of ways to see the User/Security Module - Report access matrix.
1 - From the T4 Users Admin tool - Accessible Reports Result tab - shows all reports that are accessible by the user based on their security module permissions.
2 - From the T4 Users Admin tool - Module Permission Result tab - shows the security modules the User has access to based on the T4UserGroups that have been assigned to them.
3 - From the Flex Reports Admin tool - Users With Access tab - shows all users that have access to that report. Please note, this tab can take a few moments to load as it calculates all users with permissions to the report.
Do Flex Reports consider a User's Search Criteria?
The TBS system supports a Search Criteria feature that will filter the results of all data viewed inside the system. This same search criteria is automatically added to each Flex Report, including the Accounting System Reports to ensure that TBS users are unable to access restricted data.
Search Criteria is setup using the T4Users Admin Tool. No other additional setup is needed for this feature to be enabled.
How do I see all the Flex Reports setup in TBS?
Only Administrators can see all reports setup in TBS. Flex Reports Maintenance tools are available within TBS under the Admin - Administration Menu (new) link.
Use the Flex Reports link on the left sidebar to view an alphabetized list of all Flex Reports setup in the system. This list will only show Active (non-deleted) reports.
**Please note that TBS does support the ability to have Flex Reports in external databases (ex. Billing History). Be sure to take note of the Control Database that will be stated at the top of the page within the blue information bar prior to making any changes to Flex Reports.**
Clicking the Select link next to the corresponding Flex Report will provide access to the Report SQL, Report Parameters, Report Groups, Permission Modules, Order by settings and a list of Users with Access.
How do I see all Reports a User has access to?
Flex Reports are tightly integrated into the security module of TBS. Flex Reports uses the same T4Groups security modules as those on the Users maintenance screen. Therefore, Users automatically have access to reports based on the Flex Modules tied to the Reports. For instance, if a user has access to the Customers security module, they will have access to any / all reports that are setup with the Flex Module "Customers".
To view all accessible reports click the Accessible Reports Result tabfrom the T4Users Admin Tool. This tab will show all reports that are accessible by the user based on their security module permissions.
How do I turn on a Flex Report for a Customer, Wholesale or Agent user?
Flex Reports can also be turned on for a specific user portal in TBS. For each portal type, the appropriate security modules must be added to the Flex Report.
Security modules for each portal have been already setup for:
- CustomerPortal - Customers online portal
- AgentPortal - Agents online portal
- WholesalePortal - Wholesale online portal
These portals must then be attached to the desired Flex Reports (see What permissions are needed to view Flex Reports?
Example: Any flex reports that should be shown on the customer portal will need to have the CustomerPortal module attached to it.
Reports will automatically filter results based on the user (customer, agent, wholesale) logged in.
Note: Reports can not be setup for a specific customer. All reports belonging to the Customer Portal group will show for all customers.
Can Flex Reports pull data from other database sources?
The Flex Reports engine supports pulling data from TBS and all system supporting databases (Billing History, Metrics, etc.).
In addition, Flex Reports and Flex Parameters can be setup to use other local data sources if available.
Please be sure to contact Precision support for assistance setting up this special feature.
Why does the Flex Report only return 1000 records?
Simple Answer: To increase performance.
Flex Reports are designed to pull complex queries from the TBS and supporting databases. Often times, the data results can be quite intense and cause a performance hit to the web server displaying the report. Large accounting reports can even cause the servers to time out prior to showing any results and may have negative impacts on other portions of the system while trying to generate these types of reports.
Flex Reports are designed with this issue in mind. Flex Reports support what is called the TBS Backend Service. This Backend Service essentially takes the load off the web server front end and uses a specially designed performance feature to balance the load being requested from the main TBS site as well as the requested report.
In addition, the Flex Report engine is intelligent enough to determine all records in the system that match the query requested, however by design will only return the top 1000 records to the web client for review. All rows are available for Export/Download using the Export Results feature.